<html>
<head>
<title>PGDump (PostgreSQL SQL Dump)</title>
</head>

<body bgcolor="#ffffff">

<h1>PostgreSQL SQL Dump</h1>

(GDAL/OGR >= 1.8.0)<p>

This write-only driver implements support for generating a SQL dump file that
can later be injected into a live PostgreSQL instance. It supports
PostgreSQL extended with the 
<a href="http://postgis.net/">PostGIS</a> geometries.<p>

This driver is very similar to the PostGIS shp2pgsql utility.<p>

Most creation options are shared with the regular PostgreSQL driver.<p>

<h2>Creation options</h2>

<h3>Dataset Creation Options</h3>

<ul>
<li> <b>LINEFORMAT</b>:
By default files are created with the line
termination conventions of the local platform (CR/LF on win32 or 
LF on all other systems).  This may be overridden through use of the
LINEFORMAT layer creation option which may have a value of <b>CRLF</b>
(DOS format) or <b>LF</b> (Unix format). <p>
</ul>

<h3>Layer Creation Options</h3>

<ul>
<li>
<b>GEOM_TYPE</b>: The GEOM_TYPE layer creation option can be set to 
one of "geometry" or "geography" (PostGIS >= 1.5) to force the type of geometry used for
a table. "geometry" is the default value.<p>
<li> <b>LAUNDER</b>: This may be "YES" to force new fields created on this
layer to have their field names "laundered" into a form more compatible with
PostgreSQL.  This converts to lower case and converts some special characters
like "-" and "#" to "_".  If "NO" exact names are preserved.  
The default value is "YES".  If enabled the table (layer) name will also be laundered.<p>
<li> <b>PRECISION</b>: This may be "YES" to force new fields created on this
layer to try and represent the width and precision information, if available
using NUMERIC(width,precision) or CHAR(width) types.  If "NO" then the types
FLOAT8, INTEGER and VARCHAR will be used instead.  The default is "YES".<p>
<li> <b>DIM={2,3}</b>: Control the dimension of the layer.  Defaults to 3. 
Important to set to 2 for 2D layers with PostGIS 1.0+ as it has constraints
on the geometry dimension during loading.<p>
<li> <b>GEOMETRY_NAME</b>: Set name of geometry column in new table.  If 
omitted it defaults to <i>wkb_geometry</i> for GEOM_TYPE=geometry, or <i>the_geog</i> for GEOM_TYPE=geography.<p>
<li> <b>SCHEMA</b>: Set name of schema for new table.
Using the same layer name in different schemas is supported, but not in the public schema and others.<p>
<li> <b>CREATE_SCHEMA</b>: (OGR >= 1.8.1) To be used in combination with SCHEMA. Set to ON by default so that
the CREATE SCHEMA instruction is emitted. Turn to OFF to prevent CREATE SCHEMA from being emitted.<p>
<li> <b>SPATIAL_INDEX</b>:  Set to ON by default. Creates a spatial index on the geometry column
to speed up queries. Set to OFF to disable. (Has effect only when PostGIS is available).<p>
<li> <b>TEMPORARY</b>: Set to OFF by default. Creates a temporary table instead of a permanent one.<p>
<li> <b>WRITE_EWKT_GEOM</b>: Set to OFF by default. Turn to ON to write EWKT geometries instead of HEX geometries.
This option will have no effect if PG_USE_COPY environment variable is to YES.<p>
<li> <b>CREATE_TABLE</b>: Set to ON by default so that tables are recreated if necessary. Turn to OFF to disable this and use existing table structure.<p>
<li> <b>DROP_TABLE</b>: (OGR >= 1.8.1) Set to ON by default so that tables are destroyed before being recreated.
Set to OFF to prevent DROP TABLE from being emitted. Set to IF_EXISTS in order DROP TABLE IF EXISTS to be emitted (needs PostgreSQL >= 8.2)<p>
<li> <b>SRID</b>: Set the SRID of the geometry. Defaults to -1, unless a SRS is associated with the layer. In the case, if the EPSG code is mentionned, it will be used as the SRID. (Note: the spatial_ref_sys table must be correctly populated with the specified SRID) <p>
<li> <b>NONE_AS_UNKNOWN</b>: (From GDAL 1.9.0) Can bet set to TRUE to force non-spatial layers (wkbNone) to be created as
spatial tables of type GEOMETRY (wkbUnknown), which was the behaviour prior to GDAL 1.8.0. Defaults to NO, in which case
a regular table is created and not recorded in the PostGIS geometry_columns table.<p>
<li> <b>FID</b>: (From GDAL 1.9.0) Name of the FID column to create. Defaults to 'ogc_fid'.<p>
<li> <b>EXTRACT_SCHEMA_FROM_LAYER_NAME</b>: (From GDAL 1.9.0) Can be set to NO to avoid considering the dot character
as the separator between the schema and the table name. Defaults to YES.<p>
<li> <b>COLUMN_TYPES</b>: (From GDAL 1.10) A list of strings of format field_name=pg_field_type (separated by comma)
that should be use when CreateField() is invoked on them. This will override the default choice that OGR would have made.
This can for example be used to create a column of type <a href="http://www.postgresql.org/docs/9.0/static/hstore.html">HSTORE</a>.<p>
<li> <b>POSTGIS_VERSION</b>: (From GDAL 1.9.0) Can be set to 2.0 for PostGIS 2.0 compatibility. For now, it is not
critical to set it. Its effect is just to avoid a few warnings.<p>
</ul>

<h3>Environment variables</h3>

<ul>
<li><b>PG_USE_COPY</b>: This may be "YES" for using COPY for inserting data to Postgresql.
COPY is less robust than INSERT, but significantly faster.</li><p>
</ul>

<h3>VSI Virtual File System API support</h3>

(Some features below might require OGR >= 1.9.0)<p>

The driver supports rwriting to files managed by VSI Virtual File System API, which include
"regular" files, as well as files in the /vsizip/, /vsigzip/ domains.<p>

Writing to /dev/stdout or /vsistdout/ is also supported.<p>

<h3>Example</h3>

<ul>
<li>
Simple translation of a shapefile into PostgreSQL into a file abc.sql.  The table 'abc' will
be created with the features from abc.shp and attributes from abc.dbf. The SRID is specified.
PG_USE_COPY is set to YES to improve the peformance.<p>

<pre>
% ogr2ogr --config PG_USE_COPY YES -f PGDump abc.sql abc.shp -lco SRID=32631
</pre>

<li>
Pipe the output of the PGDump driver into the psql utility.

<pre>
% ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ abc.shp | psql -d my_dbname -f -
</pre>

</ul>

<h3>See Also</h3>

<ul>
<li> <a href="drv_pg.html">OGR PostgreSQL driver Page</a><p>
<li> <a href="http://www.postgresql.org/">PostgreSQL Home Page</a><p>
<li> <a href="http://postgis.net/">PostGIS</a><p>
<li> <a href="http://trac.osgeo.org/postgis/wiki/UsersWikiOGR">PostGIS / OGR Wiki Examples Page</a><p>
</ul>

</body>
</html>
